package com.example.unisys.utils;

import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import com.example.unisys.entity.StuCourse;
import com.example.unisys.entity.Student;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

public class ConfigExcelUtil {


    //真正用来解析学生excel的函数
    public static List<Student> readExcel2(MultipartFile file) {
        List<Student> result = new ArrayList<>();
        String filename = file.getOriginalFilename();
        try {

            InputStream inputStream = file.getInputStream();
            Workbook wb;
            if (filename.matches("^.+\\.(?i)(xlsx)$")) {
                wb = new XSSFWorkbook(inputStream);
            } else {
                wb = new HSSFWorkbook(inputStream);
            }
            Sheet sheet = wb.getSheetAt(0);
            if (sheet == null) {
                return result;
            }

            // 列数
            int column = sheet.getRow(0).getPhysicalNumberOfCells();


            // 行数
            int rows = sheet.getLastRowNum();
            //LOGGER.debug("Excel行数：" + rows);
            // 此处可判断Excel行数是否符合要求

            // 循环Excel
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                Student tmp = Student.builder().build();
                if (row.getCell(0) != null) {
                    Cell cell = row.getCell(0);
                    cell.setCellType(CellType.STRING);
                    String exam_id = cell.getStringCellValue().trim();
                    tmp.setExam_id(exam_id);
                    tmp.setUsername(exam_id);
                }
                if (row.getCell(1) != null) {
                    Cell cell = row.getCell(1);
                    cell.setCellType(CellType.STRING);
                    String real_name = cell.getStringCellValue().trim();
                    tmp.setReal_name(real_name);
                }
                if (row.getCell(2) != null) {
                    Cell cell = row.getCell(2);
                    cell.setCellType(CellType.STRING);
                    String id_card = cell.getStringCellValue().trim();
                    tmp.setId_card(id_card);
                    tmp.setPassword(id_card.substring((id_card.length() - 6), id_card.length()));
                }
                if (row.getCell(12) != null) {
                    Cell cell = row.getCell(12);
                    cell.setCellType(CellType.STRING);
                    String major = cell.getStringCellValue().trim();
                    tmp.setMajor(major);
                }
                if (row.getCell(15) != null) {
                    Cell cell = row.getCell(15);
                    cell.setCellType(CellType.STRING);
                    String main_school = cell.getStringCellValue().trim();
                    tmp.setMain_school(main_school);
                }
                if (row.getCell(17) != null) {
                    Cell cell = row.getCell(17);
                    cell.setCellType(CellType.STRING);
                    String sub_school = cell.getStringCellValue().trim();
                    tmp.setSub_school(sub_school);
                }
                //if(tmp.getExam_id().trim().equals(""))
                //continue;
                result.add(tmp);
            }
            return result;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static List<Student> readExcel(MultipartFile file) {
        List<Student> result = new ArrayList<>();
        String filename = file.getOriginalFilename();
        try {

            InputStream inputStream = file.getInputStream();
            Workbook wb;
            if (filename.matches("^.+\\.(?i)(xlsx)$")) {
                wb = new XSSFWorkbook(inputStream);
            } else {
                wb = new HSSFWorkbook(inputStream);
            }
            Sheet sheet = wb.getSheetAt(0);
            if (sheet == null) {
                return result;
            }

            // 列数
            int column = sheet.getRow(0).getPhysicalNumberOfCells();


            // 行数
            int rows = sheet.getLastRowNum();
            //LOGGER.debug("Excel行数：" + rows);
            // 此处可判断Excel行数是否符合要求

            // 循环Excel
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                Student tmp = Student.builder().build();
                if (row.getCell(0) != null) {
                    Cell cell = row.getCell(0);
                    cell.setCellType(CellType.STRING);
                    String real_name = cell.getStringCellValue().trim();
                    tmp.setReal_name(real_name);
                }
                if (row.getCell(1) != null) {
                    Cell cell = row.getCell(1);
                    cell.setCellType(CellType.STRING);
                    String exam_id = cell.getStringCellValue().trim();
                    tmp.setExam_id(exam_id);
                    tmp.setUsername(exam_id);
                }
                if (row.getCell(2) != null) {
                    Cell cell = row.getCell(2);
                    cell.setCellType(CellType.STRING);
                    String real_name = cell.getStringCellValue().trim();
                    tmp.setReal_name(real_name);
                }
                if (row.getCell(3) != null) {
                    Cell cell = row.getCell(3);
                    cell.setCellType(CellType.STRING);
                    String password = cell.getStringCellValue().trim();
                    tmp.setPassword(password);
                }
                if (row.getCell(4) != null) {
                    Cell cell = row.getCell(4);
                    cell.setCellType(CellType.STRING);
                    String id_card = cell.getStringCellValue().trim();
                    tmp.setId_card(id_card);
                }
                if (row.getCell(5) != null) {
                    Cell cell = row.getCell(5);
                    cell.setCellType(CellType.STRING);
                    String main_school = cell.getStringCellValue().trim();
                    tmp.setMain_school(main_school);
                }
                if (row.getCell(6) != null) {
                    Cell cell = row.getCell(6);
                    cell.setCellType(CellType.STRING);
                    String sub_school = cell.getStringCellValue().trim();
                    tmp.setSub_school(sub_school);
                }
                if (row.getCell(7) != null) {
                    Cell cell = row.getCell(7);
                    cell.setCellType(CellType.STRING);
                    String phone = cell.getStringCellValue().trim();
                    tmp.setPhone(phone);
                }
                if (row.getCell(8) != null) {
                    Cell cell = row.getCell(8);
                    cell.setCellType(CellType.STRING);
                    String wechat_num = cell.getStringCellValue().trim();
                    tmp.setWechat_num(wechat_num);
                }
                if (row.getCell(9) != null) {
                    Cell cell = row.getCell(9);
                    cell.setCellType(CellType.STRING);
                    String major = cell.getStringCellValue().trim();
                    tmp.setMajor(major);
                }
                result.add(tmp);
            }
            return result;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return result;
    }

    //读入学生选课表
    public static List<StuCourse> readCourseExcel(MultipartFile file) {
        List<StuCourse> result = new ArrayList<>();
        String filename = file.getOriginalFilename();
        //System.out.println("name is"+filename);
        try {

            InputStream inputStream = file.getInputStream();
            Workbook wb;
            if (filename.matches("^.+\\.(?i)(xlsx)$")) {
                wb = new XSSFWorkbook(inputStream);
            } else {
                wb = new HSSFWorkbook(inputStream);
            }
            Sheet sheet = wb.getSheetAt(0);
            if (sheet == null) {
                return result;
            }

            // 列数
            int column = sheet.getRow(0).getPhysicalNumberOfCells();

            // 行数
            int rows = sheet.getLastRowNum();
            System.out.println("rows is" + rows);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    //System.out.println("row is null");
                    continue;
                }
                StuCourse tmp = StuCourse.builder().build();
                if (row.getCell(2) != null) {
                    Cell cell = row.getCell(2);
                    cell.setCellType(CellType.STRING);
                    String exam_id = cell.getStringCellValue().trim();
                    System.out.println("examid is " + exam_id);
                    tmp.setExam_id(exam_id);
                }

                if (row.getCell(3) != null) {
                    Cell cell = row.getCell(3);
                    cell.setCellType(CellType.STRING);
                    String course_id = cell.getStringCellValue().trim();
                    System.out.println("course_id is " + course_id);
                    tmp.setCourse_id(course_id);
                }

                if (tmp.getExam_id().trim().equals(""))
                    continue;
                if (tmp.getCourse_id() == null || tmp.getCourse_id().equals(""))
                    continue;
                result.add(tmp);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("result size is" + result.size());
        return result;

    }


    //生成学生成绩表
    public static String getStuCourseExcel(List<Map<String, Object>> ls) {
        String filepath = null;
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("sheet1");
            //创建表头
            HSSFRow row0 = sheet.createRow(0);
            row0.createCell(0).setCellValue("身份证");
            row0.createCell(1).setCellValue("姓名");
            row0.createCell(2).setCellValue("准考证");
            row0.createCell(3).setCellValue("课程代码");
            row0.createCell(4).setCellValue("课程名称");
            row0.createCell(5).setCellValue("主考学校名");
            row0.createCell(6).setCellValue("助学机构名");
            row0.createCell(7).setCellValue("专业代码");
            row0.createCell(8).setCellValue("专业名称");
            row0.createCell(9).setCellValue("课程学习成绩");//视频完成率
            row0.createCell(10).setCellValue("平时作业成绩");//4个test综合得分
            row0.createCell(11).setCellValue("综合测验成绩");//综合测验得分
            row0.createCell(12).setCellValue("学习表现成绩（网络）");//永远为0
            row0.createCell(13).setCellValue("总成绩");
            row0.createCell(14).setCellValue("学习考期（网络试点）");
            for (int i = 0; i < ls.size(); i++) {
                Map<String, Object> tmp = ls.get(i);
                HSSFRow row = sheet.createRow(i + 1);
                row.createCell(0).setCellValue((String) tmp.get("id_card"));
                row.createCell(1).setCellValue((String) tmp.get("real_name"));
                row.createCell(2).setCellValue((String) tmp.get("exam_id"));
                row.createCell(3).setCellValue((String) tmp.get("course_id"));
                row.createCell(4).setCellValue((String) tmp.get("course_name"));
                row.createCell(5).setCellValue((String) tmp.get("main_school"));
                row.createCell(6).setCellValue((String) tmp.get("sub_school"));
                Integer watch_time = (Integer) tmp.get("watch_time");
                Double total_time = (Integer) tmp.get("total_time") * 1.0;
                row.createCell(9).setCellValue(watch_time / total_time);
                Integer test1 = (Integer) tmp.get("test1");
                Integer test2 = (Integer) tmp.get("test2");
                Integer test3 = (Integer) tmp.get("test3");
                Integer test4 = (Integer) tmp.get("test4");
                double avg = (test1 + test2 + test3 + test4) / 4.0;
                row.createCell(10).setCellValue(avg);
                row.createCell(11).setCellValue((Integer) tmp.get("main_test"));
                row.createCell(12).setCellValue(0);
                row.createCell(14).setCellValue((String) tmp.get("tag"));
            }

            File fileDir = UploadUtil.getImgDirFile();
            // 输出文件夹绝对路径  -- 这里的绝对路径是相当于当前项目的路径而不是“容器”路径

            // System.out.println(fileDir.getAbsolutePath());
            String uuid = UUID.randomUUID().toString().replaceAll("-", "");
            filepath = fileDir.getAbsolutePath() + File.separator + uuid + ".xls";
            //filepath="/Users/yanhong/Desktop/"+uuid+".xls";
            File tmpfile = new File(filepath);
            FileOutputStream output = new FileOutputStream(filepath);
            wb.write(output);
            output.close();
            System.out.println("生成文件路径为" + filepath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return filepath;
    }

    //生成督学excel
    public static String getSuperviseExcel(List<Map<String, Object>> ls) {
        String filepath = null;
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("sheet1");
            //创建表头
            HSSFRow row0 = sheet.createRow(0);
            row0.createCell(0).setCellValue("身份证");
            row0.createCell(1).setCellValue("姓名");
            row0.createCell(2).setCellValue("准考证");
            row0.createCell(3).setCellValue("主考学校名");
            row0.createCell(4).setCellValue("助学机构名");
//            row0.createCell(5).setCellValue("专业名称");
            row0.createCell(5).setCellValue("上次学习时间");
            row0.createCell(6).setCellValue("未学习时间间隔");

            for (int i = 0; i < ls.size(); i++) {
                Map<String, Object> tmp = ls.get(i);
                HSSFRow row = sheet.createRow(i + 1);
                try {
                    row.createCell(0).setCellValue((String) tmp.get("id_card"));
                    row.createCell(1).setCellValue((String) tmp.get("real_name"));
                    row.createCell(2).setCellValue((String) tmp.get("exam_id"));
                    row.createCell(3).setCellValue((String) tmp.get("main_school"));
                    row.createCell(4).setCellValue((String) tmp.get("sub_school"));
                    row.createCell(5).setCellValue((String) tmp.get("recent_login"));
                    String curDate = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date());
                    String recentLogin = (String) tmp.get("recent_login");
                    if (recentLogin != null) {
                        long days = between_days(curDate, recentLogin);
                        row.createCell(6).setCellValue(String.valueOf(days));
                    }
                    row.createCell(6).setCellValue("从未登录");

                }catch (Exception e){
                    e.printStackTrace();
                }
            }

            File fileDir = UploadUtil.getImgDirFile();
            // 输出文件夹绝对路径  -- 这里的绝对路径是相当于当前项目的路径而不是“容器”路径

            // System.out.println(fileDir.getAbsolutePath());
            String uuid = UUID.randomUUID().toString().replaceAll("-", "");
            filepath = fileDir.getAbsolutePath() + File.separator + uuid + "supervise.xls";
            //filepath="/Users/yanhong/Desktop/"+uuid+".xls";
            File tmpfile = new File(filepath);
            FileOutputStream output = new FileOutputStream(filepath);
            wb.write(output);
            output.close();
            System.out.println("生成文件路径为" + filepath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return filepath;
    }
    public static Long between_days(String a, String b) {

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");// 自定义时间格式

        Calendar calendar_a = Calendar.getInstance();// 获取日历对象
        Calendar calendar_b = Calendar.getInstance();

        Date date_a = null;
        Date date_b = null;

        try {
            date_a = simpleDateFormat.parse(a);//字符串转Date
            date_b = simpleDateFormat.parse(b);
            calendar_a.setTime(date_a);// 设置日历
            calendar_b.setTime(date_b);
        } catch (ParseException e) {
            e.printStackTrace();//格式化异常
        }

        long time_a = calendar_a.getTimeInMillis();
        long time_b = calendar_b.getTimeInMillis();

        long between_days = (time_b - time_a) / (1000 * 3600 * 24);//计算相差天数

        return between_days;
    }

}

